package com.motiv.classtosql.utils;

import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.doudian.open.annotation.OpField;
import com.doudian.open.gson.annotations.SerializedName;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.List;

/**
 * Description:
 * Author: 可乐
 * Date: 2021/12/28 11:44
 */
public class ToSQLSERVERUtil {
    //表描述sql
    private static final String TABLE_DESC = "EXEC sp_addextendedproperty N'TabName', N'%s', N'user', N'dbo', N'table', N'%s', null,null\n";
    //字段描述sql
    private static final String COLUMN_DESC = "EXEC sp_addextendedproperty N'MS_Description', N'%s', N'user', N'dbo', N'table', N'%s', N'column', N'%s'\n";

    /**
     * 获取表描述SQL
     *
     * @param desc      表描述
     * @param tableName 表名称
     * @return sql
     */
    public static String GetTableDescSql(String desc, String tableName) {
        return String.format(TABLE_DESC, desc, tableName);
    }

    /**
     * 获取字段描述SQL
     *
     * @param desc       字段描述
     * @param tableName  表名称
     * @param columnName 字段名称
     * @return sql
     */
    public static String GetColumnDescSql(String desc, String tableName, String columnName) {
        return String.format(COLUMN_DESC, desc, tableName, columnName);
    }


    /**
     * @apiNote 获取数据类型
     * @author 可乐
     * @date 2022/1/5 9:44
     * @param type type
     * @return 数据类型
     */
    public static String GetDBDataType(Class<?> type) {
        String dataType = "";
        if (type == Integer.class || type == int.class) {
            dataType = "INT";
        } else if (type == Float.class || type == float.class) {
            dataType = "DECIMAL(18,2)";
        } else if (type == Byte.class || type == byte.class) {
            dataType = "BIT";
        } else if (type == Double.class || type == double.class) {
            dataType = "DECIMAL(18,2)";
        } else if (type == Long.class || type == long.class) {
            dataType = "BIGINT";
        } else if (type == Character.class) {
            dataType = "VARCHAR";
        } else if (type == Boolean.class || type == boolean.class) {
            dataType = "BIT";
        } else if (type == Short.class || type == short.class) {
            dataType = "INT";
        } else if (type == String.class) {
            dataType = "NVARCHAR(200)";
        }
        /*else {
            //引用类型、数组、map、list等
            dataType = "NVARCHAR(MAX)";
        }*/
        return dataType;
    }

    /**
     * @apiNote 根据beanClass 获取创建表的sql
     * @author 可乐
     * @date 2021/12/28 15:11
     *
     * @param beanClass beanClass
     * @param fkeyName  外键名称
     * @param level     结构的层级，用于level>=3时不再建表，建个字段保存json
     * @return sql语句
     */
    public static List<String> GetCreateTableSqlList(Class<?> beanClass, String fkeyName, int level) {
        List<String> result = new ArrayList<>();
        Field[] fields = ReflectUtil.getFieldsDirectly(beanClass, false);
        //表名
        String tableName = beanClass.getName().substring(beanClass.getName().lastIndexOf(".") + 1);
        //字段
        StringBuilder sbColumn = new StringBuilder(" ID BIGINT PRIMARY KEY NOT NULL --主键id \n");

        //数据库注释
        StringBuilder dbDesc = new StringBuilder();
        dbDesc.append("--表描述信息\n");
        dbDesc.append(GetTableDescSql(tableName, tableName));
        //外键不为空
        if (StrUtil.isNotBlank(fkeyName)) {
            String desc = "外键-" + fkeyName;
            sbColumn.append(", " + fkeyName + " BIGINT NOT NULL --" + desc + "\n");
            dbDesc.append(GetColumnDescSql(desc, tableName, fkeyName));
        }

        for (Field field :
                fields) {

            //抖店api的字段以及字段描述类
            SerializedName serializedName = field.getAnnotation(SerializedName.class);
            OpField opField = field.getAnnotation(OpField.class);
            //字段名称
            String column = serializedName.value();
            //字段描述
            String columnDesc = opField.desc();
            //字段描述-示例，可为空
            String columnExample = opField.example();

            Class<?> type = field.getType();
            String dataType = GetDBDataType(type);

            if (StrUtil.isNotBlank(dataType)) {
                //数据库的描述
                String desc = columnDesc + ";example：" + columnExample;
                sbColumn.append(String.format(", %s %s NULL --%s \n", column, dataType, desc));
                dbDesc.append(GetColumnDescSql(desc, tableName, column));
            } else {
                //List泛型
                if (type == List.class) {
                    // 当前集合的泛型类型
                    Type genericType = field.getGenericType();
                    if (null == genericType) {
                        continue;
                    }
                    if (genericType instanceof ParameterizedType) {
                        ParameterizedType pt = (ParameterizedType) genericType;
                        // 得到泛型里的class类型对象
                        Class<?> genericClass = (Class<?>) pt.getActualTypeArguments()[0];
                        //是基本类型的集合，如List<String>，就直接生成一个字段
                        String dataType2 = GetDBDataType(genericClass);
                        if (StrUtil.isNotBlank(dataType2)) {
                            dataType2 = "NVARCHAR(1000)";
                            //数据库的描述
                            String desc = columnDesc + ";example：" + columnExample + "****List<" + genericClass.getTypeName() + ">,可适当调整长度,保存json****";
                            sbColumn.append(String.format(", %s %s NULL --%s \n", column, dataType2, desc));
                            dbDesc.append(GetColumnDescSql(desc, tableName, column));
                            continue;
                        }
                        //第三层级，不再建表，建个字段保存json
                        if (level >= 3) {
                            //数据库的描述
                            String desc = columnDesc + ";example：" + columnExample + "****第三层级，保存json****";
                            sbColumn.append(String.format(", %s NVARCHAR(MAX) NULL --%s \n", column, desc));
                            dbDesc.append(GetColumnDescSql(desc, tableName, column));
                            continue;
                        }
                        //外键名
                        String tempFKeyName = tableName + "ID";
                        //递归，生成一张子表
                        result.addAll(GetCreateTableSqlList(genericClass, tempFKeyName, level + 1));
                    }
                } else {
                    Class<?> aClass = null;
                    try {
                        aClass = Class.forName(type.getName());
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                    //第三层级，不再建表，建个字段保存json
                    if (level >= 3) {
                        //数据库的描述
                        String desc = columnDesc + ";example：" + columnExample + "****第三层级，保存json****";
                        sbColumn.append(String.format(", %s NVARCHAR(MAX) NULL --%s \n", column, desc));
                        dbDesc.append(GetColumnDescSql(desc, tableName, column));
                        continue;
                    }
                    //外键名
                    String tempFKeyName = tableName + "ID";
                    //递归，生成一张子表
                    result.addAll(GetCreateTableSqlList(aClass, tempFKeyName, level + 1));
                }
            }
        }

        sbColumn.append(",CreateTime DATETIME  NOT NULL --创建时间 \n");
        sbColumn.append(",LastEditTime DATETIME  NOT NULL --最后修改时间 \n");

        String sql = "CREATE TABLE " + tableName + "( \n" + sbColumn.toString() + "\n )\n" + dbDesc.toString();
        result.add(sql);
        return result;
    }

}
